/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.edu.thptduongbachmai.DbUtil;

import com.vng.zing.logger.ZLogger;
import java.io.Serializable;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import org.apache.log4j.Logger;
import vn.edu.thptduongbachmai.model.pojo.Users;
import vn.edu.thptduongbachmai.services.DBService;
import vn.edu.thptduongbachmai.services.MySQLService;

/**
 *
 * @author Son
 */
public class DatabaseUtil {

    private static final Logger _Logger = ZLogger.getLogger(DatabaseUtil.class);
    private DBService _dBService;
    private static String SELECTALL = "select * from ;;";
    private static String SELECT = "SELECT ; FROM `;;` ;;";
    public static String INSERT_QUERY = "INSERT INTO ;; ; VALUES ;";

    public DatabaseUtil() {
        _dBService = MySQLService.getInstance("BaseModel");

    }
    public static DatabaseUtil Instance = getInstance();

    public PreparedStatement makeBasicStatement(String query) {
        Connection conn = _dBService.getDbConnection();
        PreparedStatement pStmt = null;
        try {
            pStmt = conn.prepareStatement(query);
        } catch (SQLException ex) {
            _Logger.error(ex.getMessage(), ex);
        }
        return pStmt;

    }

    public PreparedStatement makeInsertStatement(Class type, Object obj) {
        Connection conn = _dBService.getDbConnection();
        String tableName = getTableName(type);
        Map<String, Object> fields = getFields(type, obj);
        String fieldNames = "(";
        String values = "(";
        int length = fields.size();
        String[] names = new String[length];
        int i = 0;
        for (Map.Entry<String, Object> entry : fields.entrySet()) {
            String string = entry.getKey();
            if (i > 0) {
                fieldNames += ",`" + string + "`";
                values += ",?";
            } else {
                fieldNames += "`" + string + "`";
                values += "?";
            }
            names[i] = string;
            i++;
        }
        fieldNames += ")";
        values += ")";

        String query = INSERT_QUERY.replaceFirst(";;", tableName);
        query = query.replaceFirst(";", fieldNames);
        query = query.replaceFirst(";", values);
        System.out.println(query);
        PreparedStatement pStmt = null;
        i = 0;
        try {
            i++;
            pStmt = conn.prepareStatement(query);
            for (int j = 0; j < length; j++) {
                String field = names[j];
                
                Object object = fields.get(field);
                pStmt.setObject(j+1, object);
            }
        } catch (SQLException ex) {
            _Logger.error(ex.getMessage(), ex);
        }
        
        return pStmt;


    }

    public PreparedStatement makeSelectStatement(Class type, Criteria criteria) {
        Connection conn = _dBService.getDbConnection();
        String tableName = getTableName(type);
        String criteriaString = criteria.toString();
        String query = SELECT.replaceFirst(";;", tableName).replaceFirst(";;", criteriaString);
        if (criteria.getProjection() == null) {
            query = query.replace(";", "*");
        } else {
            query = query.replace(";", criteria.getProjection().toString());
        }
        System.out.println(query);
        PreparedStatement pStmt = null;
        try {
            int length = criteria.list.size();
            pStmt = conn.prepareStatement(query);
            for (int i = 1; i < length + 1; i++) {
                SimpleExpression simpleExpression = criteria.list.get(i - 1);
                Object value = simpleExpression.getValue();
                if (simpleExpression.isLikeOp()) {
                    value = "%" + value.toString() + "%";
                }
                pStmt.setObject(i, value);
            }
        } catch (SQLException ex) {
            _Logger.error(ex.getMessage(), ex);
        }
        return pStmt;

    }

    public Map<String, Object> getFields(Class type, Object obj) {

        try {

            try {
                Method prepaMethod = type.getDeclaredMethod("getFields");
                prepaMethod.setAccessible(true);
                Map<String, Object> result = (Map<String, Object>) prepaMethod.invoke(obj);  //tao statement
                return result;
            } catch (NoSuchMethodException methodException) {
                _Logger.info(methodException + ": ");
            } catch (IllegalArgumentException illegalArgumentException) {
                _Logger.info(illegalArgumentException + ":");
            }

        } catch (Exception ex) {
            _Logger.error(ex.getMessage(), ex);
        }
        return null;

    }

    private String getTableName(Class type) {

        try {
            Object obj = type.newInstance();
            try {
                Method prepaMethod = type.getDeclaredMethod("getTableName");
                prepaMethod.setAccessible(true);
                String tableName = (String) prepaMethod.invoke(obj);  //tao statement
                return tableName;
            } catch (NoSuchMethodException methodException) {
                _Logger.info(methodException + ": ");
            } catch (IllegalArgumentException illegalArgumentException) {
                _Logger.info(illegalArgumentException + ":");
            }

        } catch (Exception ex) {
            _Logger.error(ex.getMessage(), ex);
        }
        return null;

    }

    private String getKeyName(Class type) {

        try {
            Object obj = type.newInstance();
            try {
                Method prepaMethod = type.getDeclaredMethod("getKeyName");
                prepaMethod.setAccessible(true);
                String keyName = (String) prepaMethod.invoke(obj);  //tao statement
                return keyName;
            } catch (NoSuchMethodException methodException) {
                _Logger.info(methodException + ": ");
            } catch (IllegalArgumentException illegalArgumentException) {
                _Logger.info(illegalArgumentException + ":");
            }

        } catch (Exception ex) {
            _Logger.error(ex.getMessage(), ex);
        }
        return null;

    }

    public boolean executeUpdate(Class type, Object obj) {
        boolean ret = false;
        Connection conn = _dBService.getDbConnection();
        try {
            int canTry = 3;
            do {
                try {

                    PreparedStatement statement = makeInsertStatement(type, obj);
                    int result = statement.executeUpdate();
                    if (result >= 0) {
                        ret = true;
                    }
                    canTry = 0;
                } catch (SQLException sqlEx) {
                    _Logger.info(sqlEx + ": " + canTry);
                    --canTry;
                }
            } while (canTry > 0);

        } catch (Exception ex) {
            _Logger.error(ex.getMessage(), ex);
        } finally {
            _dBService.releaseDbConnection(conn);
        }
        return ret;
    }

    /**
     * Thực hiện try vấn dữ liệu từ database theo câu statement được định sẵn
     *
     * @param statement
     * @return 1 resultset
     */
    private ResultSet getToResultSet(PreparedStatement statement) {
        ResultSet result;

        Connection conn = _dBService.getDbConnection();
        try {

            int canTry = 3;
            do {

                try {
                    result = statement.executeQuery();
                    canTry = 0;//canTry = 0;
                    return result;
                } catch (SQLException sqlEx) {
                    _Logger.info(sqlEx + ": " + canTry);
                    --canTry;
                }
            } while (canTry > 0);

        } catch (Exception ex) {
            _Logger.error(ex.getMessage(), ex);
        } finally {
            _dBService.releaseDbConnection(conn);
        }

        return null;
    }

    /**
     * Đẩy từ resultSet sang 1 list object
     *
     * @param result
     * @param begin bắt đầu từ 0
     * @param end nếu -1 là dòng cuối cùng , nếu end vượt quá số dòng thì sẽ là
     * dòng cuối
     * @return List object
     */
    private List pullDataToListObject(Class type, ResultSet result) {
        List resultList = new ArrayList();
        boolean flag = true;
        try {
            int i = 0;
            int numRow = result.getRow();
            while (i < numRow || flag) {
                Object p = pullDataToObject(type, result);
                if (p == null) {
                    break;
                }
                resultList.add(p);
            }
        } catch (SQLException sQLException) {
            _Logger.info(sQLException + ": ");

        }
        return resultList;
    }

    /**
     * Đẩy 1 ResultSet to object
     *
     * @param result
     * @return
     */
    private Object pullDataToObject(Class type, ResultSet result) {
        Object resultObject, obj;
        try {
            obj = type.newInstance();
            try {
                Method getDataMethod = type.getDeclaredMethod("getDataFromResultSet", ResultSet.class);
                try {
                    if (result == null) {
                        return null;
                    }
                    while (result.next()) {
                        resultObject = getDataMethod.invoke(obj, result);
                        return resultObject;

                    }

                } catch (SQLException sQLException) {
                    _Logger.info(sQLException + ": ");
                }
            } catch (NoSuchMethodException methodException) {
                _Logger.info(methodException + ": ");
            } catch (IllegalArgumentException illegalArgumentException) {
                _Logger.info(illegalArgumentException + ":");
            }
        } catch (Exception ex) {
            _Logger.error(ex.getMessage(), ex);

        }
        return null;
    }

    public Object get(Class type, Serializable key) {
        String keyName = getKeyName(type);
        Criteria criteria = new Criteria();
        criteria.add(Restrictions.eq(keyName, key));
        List result = getWithCriteria(type, criteria);
        return result.get(0);
    }

    public List getAll(Class type) {
        List result = null;
           Connection conn=null;
    
        String tableName = "`" + getTableName(type) + "`";
        String query = SELECTALL.replaceFirst(";;", tableName);
        PreparedStatement statement = makeBasicStatement(query);
        ResultSet resultSet = getToResultSet(statement);
        result = pullDataToListObject(type, resultSet);
        
             try {
            conn = statement.getConnection();
        } catch (SQLException ex) {
             _Logger.error(ex.getMessage(), ex);
        }
        
        _dBService.releaseDbConnection(conn);
        return result;
    }

    public List getFromQuery(Class type, String query) {
        List result = null;
        Connection conn=null;
        PreparedStatement statement = makeBasicStatement(query);
        ResultSet resultSet = getToResultSet(statement);
        result = pullDataToListObject(type, resultSet);
        try {
            conn = statement.getConnection();
        } catch (SQLException ex) {
             _Logger.error(ex.getMessage(), ex);
        }
        
        _dBService.releaseDbConnection(conn);
        return result;
    }

    public List getWithCriteria(Class type, Criteria criteria) {
        List result = null;
        Connection conn=null;
        PreparedStatement statement = makeSelectStatement(type, criteria);
        ResultSet resultSet = getToResultSet(statement);
        if (criteria.getProjection() == null) {
            result = pullDataToListObject(type, resultSet);
        } else {
            result = new ArrayList<Long>();
            try {
                if (resultSet.first()) {
                    result.add(resultSet.getLong(1));
                }
                conn= statement.getConnection();
                
            } catch (SQLException sqle) {
            }finally{
                
                _dBService.releaseDbConnection(conn);
            }
                  
            
        }
            
        return result;
    }

    public static DatabaseUtil getInstance() {
        if (Instance == null) {
            Instance = new DatabaseUtil();
        }
        return Instance;
    }
}
